<?php

namespace app\studentLayer\model;

use think\Db;
use think\Exception;

class StudentModel
{


    public static function SelectStudentModel(array $where = null)
    {
        /*
                select s.*,cls.`name` from s_student as s
                LEFT JOIN s_classes as cls on s.cls_id = cls.id
        $result = Db::table('s_student')->alias('s')->field('s.*,cls.`name` as cls_name')
            ->leftJoin('s_classes cls', 's.cls_id = cls.id')->select();
*/

        /*
                SELECT
                    s.*, GROUP_CONCAT(c. NAME, t. NAME) AS c_t_name,
                    cls.`name` AS cls_name
                FROM
                    s_student AS s
                LEFT JOIN s_classes AS cls ON s.cls_id = cls.id
                LEFT JOIN s_report AS r ON s.id = r.s_id
                LEFT JOIN s_course AS c ON c.id = r.c_id
                LEFT JOIN s_teacher AS t ON t.id = r.t_id
                GROUP BY
                    s.id
        */
        //草它老母，这个tp框架有毒，妈的老子找了久，一直提示表名不是唯一，结果发现妈的，原来是 leftJoin()  as 表名时候多空了按了一个空格，草

        //var_dump($where);die;
        $result = Db::table('s_student')->alias('s')
            ->field('s.*, GROUP_CONCAT(c. NAME, t. NAME) AS c_t_name,
            cls.`name` AS cls_name')
            ->leftJoin('s_classes cls', 's.cls_id = cls.id')
            ->leftJoin('s_report r', 's.id = r.s_id')
            ->leftJoin('s_course c', 'c.id = r.c_id')
            ->leftJoin('s_teacher t', 't.id = r.t_id')
            ->where(1, '=', 1);


        //垃圾tp框架，搞我半天，他妈的，原来where不需要加上 and 草拟吗

        if ($where['c_t_name'] && isset($where['c_t_name'])) {
//            and c.name LIKE "%数学%"
            $c_name = $where['c_t_name'];
            $result->where('c.name', 'LIKE', "%" . $c_name . "%");

        }

        if ($where['name'] && isset($where['name'])) {
            //var_dump(1);die;
            $name = $where['name'];
            $result->where('s.name', 'like', "%" . $name . "%");
//            echo Db::getLastSql();die;
        }
        //var_dump($where,$c_name,$name);die;

       // $result = $result->group('s.id')->select();

        return $result;
    }


    public static function InsertStudentModel(array $Insert)
    {


        //开启事务
        Db::startTrans();
        try {


            //科目id
            $c_id = $Insert['c_id'];
            $report['cls_id'] = $Insert['cls_id'];
            unset($Insert['c_id']);
            //var_dump($Insert);die;

            //对学生表进行添加
            $s_id = Db::table('s_student')->insertGetId($Insert);
            $report['s_id'] = $s_id;
            if (!(isset($s_id) && $s_id)) {

                //回滚
                Db::rollback();
            } else {


                /*  select t.id as t_id,c.id as c_id from s_course c
                    LEFT JOIN s_teaching tch on c.id = tch.c_id
                    LEFT JOIN s_teacher t on  t.id = tch.t_id
                    where c.id = 9*/
                //var_dump($report);die;
                //查询老师的id
                foreach ($c_id as $value) {
                    //科目的id
                    $report['c_id'] = $value;

                    //这是老师的id，记住了，$t_id 跟 $t_id[] 完全不一样，对，是完全不一样
                    $t_id = Db::table('s_course')->alias('c')->field('t.id as t_id')
                        ->leftJOin('s_teaching tch', 'c.id = tch.c_id')
                        ->leftJoin('s_teacher t', 't.id = tch.t_id')
                        ->where('c.id', '=', $value)->select();

//                $t_id = Db::query('select t.id as t_id from s_course c
//                LEFT JOIN s_teaching tch on c.id = tch.c_id
//                LEFT JOIN s_teacher t on  t.id = tch.t_id
//                where c.id = '.$value);

                    //老师的id
                    foreach ($t_id as $k => $val) {
                        //因为查出来的t_id 有可能是一个数组，所以必须要带上键值查询
                        $report['t_id'] = $val['t_id'];
                        //var_dump($t_id,$report);die;
                        $result = Db::table('s_report')->insert($report);

                    }

                }

                if (isset($result) && $result) {

                    //提交
                    Db::commit();
                    return $result;

                } else {

                    //回滚
                    Db::rollback();
                }


                //var_dump($report,$t_id);die;

            }

        } catch (exception $exception) {
            Db::rollback();
        }
        return null;
    }


    public static function UpdateStudentModel(array $Update)
    {
        //var_dump($Update);die;
        //开启事务
        Db::startTrans();
        try {

            //科目id
            $c_id = $Update['c_id'];
            $report['cls_id'] = $Update['cls_id'];
            $report['s_id'] = $Update['id'];
            unset($Update['c_id']);

            //对学生表进行修改 //update s_student set age= 200 where id=177
            $start = Db::table('s_student')->where(['id'=>$Update['id']])->update($Update);
            //var_dump($report);die;
            if (!(isset($Update) && $Update)) {

                //回滚
                Db::rollback();
            } else {

                //接下来是对关联表的修改了，必须先删除原本关联表的信息，在插入新的信息就可以了
                $Delete_Report = Db::table('s_report')->where(['s_id'=>$Update['id']])->delete();

                foreach ($c_id as $value) {
                    //科目的id
                    $report['c_id'] = $value;
                    //var_dump($report);die;
                    $t_id = Db::table('s_course')->alias('c')->field('t.id as t_id')
                        ->leftJOin('s_teaching tch', 'c.id = tch.c_id')
                        ->leftJoin('s_teacher t', 't.id = tch.t_id')
                        ->where('c.id', '=', $value)->select();

                    //老师的id
                    foreach ($t_id as $k => $val) {
                        //因为查出来的t_id 有可能是一个数组，所以必须要带上键值查询
                        $report['t_id'] = $val['t_id'];
                        //var_dump($t_id,$report);die;
                        $result = Db::table('s_report')->insert($report);
                    }

                }
                if (isset($result) && $result) {

                    //提交
                    Db::commit();
                    return $result;

                } else {

                    //回滚
                    Db::rollback();
                }
            }

        } catch (exception $exception) {
            Db::rollback();
        }
        return null;
    }


    public static function DeleteStudent(array $id)
    {
        //开启事务
        Db::startTrans();
        try {

            $Select_Student = Db::table('s_student')->where(['id'=>$id['id']])->delete();
            if($Select_Student && isset($Select_Student)){
                $Delete_Report = Db::table('s_report')->where(['s_id'=>$id['id']])->delete();

                Db::commit();
                return $Select_Student;

            }else{

                //回滚
                Db::rollback();
            }

        }catch(Exception $exception){
            //回滚
            Db::rollback();
        }
    }


}